/*Replicate tables of "The securitization flash flood." 
See ReadMe.pdf which summarizes the list of data files used. 
*/

clear all
set more off
global sandbox "C:\Delete\"
global replication "C:\Users\kanda\OneDrive - Northeastern University\Projects\Securitization\Replication Packet\"

cd $sandbox
*

log using "$replication\logfile_tables", replace

/************************************************************
Table 1: Treated and Control groups within bank holding companies
******************************************************* */

u "$replication\Data\bank_holding_company_data", clear

fsum log_avg_assets repo_borrowing pvt_mbs mort_sec other_sec tier1_assets loans_ovr_assets loans_ovr_total_rw if treated==1, stats(n mean sd) label

fsum log_avg_assets repo_borrowing pvt_mbs mort_sec other_sec tier1_assets loans_ovr_assets loans_ovr_total_rw if treated==0, stats(n mean sd) label


/************************************************************
Table 2: Triparty repo contracts 
******************************************************* */

u "$replication\Data\triparty_repo_04_06", clear

tabstat amount_due, by(collateral_class) stat(N mean p50 sd min max)

tabstat amount_due_govt amount_due_corporate amount_due_mbs amount_due_paper amount_due_other if repo_qtr>177 & repo_qtr<187, by(repo_qtr_label) s(sum)

/************************************************************
Table 3: Holdings of private label MBS 
******************************************************* */

u "$replication\Data\bank_holding_company_data", clear

tab year, gen(yr_ind)

forvalues i=2002/2008 {
	
	local yrloop = `i' - 2001
	rename yr_ind`yrloop' yr_ind`i'	
	gen t_yr_ind`i' = treated * yr_ind`i'		
}
*

* Column (1)
reghdfe pvt_mbs t_yr_ind2002 t_yr_ind2003 t_yr_ind2005 ///
t_yr_ind2006 t_yr_ind2007  t_yr_ind2008 ///
, a(holding_id year) cl(holding_id)

* Column (2)
reghdfe pvt_mbs t_yr_ind2002 t_yr_ind2003 t_yr_ind2005 ///
t_yr_ind2006 t_yr_ind2007  t_yr_ind2008 ///
, a(holding_id year size) cl(holding_id)

* Column (3)
reghdfe pvt_mbs t_yr_ind2002 t_yr_ind2003 t_yr_ind2005 ///
t_yr_ind2006 t_yr_ind2007  t_yr_ind2008 tier1_assets ///
, a(holding_id year) cl(holding_id)


/************************************************************
Table 4: AFS holdings 
******************************************************* */
*
u "$replication\Data\bank_holding_company_data", clear

tab year, gen(yr_ind)

forvalues i=2002/2008 {
	
	local yrloop = `i' - 2001
	rename yr_ind`yrloop' yr_ind`i'	
	gen t_yr_ind`i' = treated * yr_ind`i'		
}
*

* Column (1)
reghdfe pvt_mbs_afs t_yr_ind2002 t_yr_ind2003 t_yr_ind2005 ///
t_yr_ind2006 t_yr_ind2007  t_yr_ind2008 ///
, a(holding_id year) cl(holding_id)

* Column (2)
reghdfe pvt_mbs_afs t_yr_ind2002 t_yr_ind2003 t_yr_ind2005 ///
t_yr_ind2006 t_yr_ind2007  t_yr_ind2008 ///
, a(holding_id year size) cl(holding_id)

* Column (3)
reghdfe pvt_mbs_afs t_yr_ind2002 t_yr_ind2003 t_yr_ind2005 ///
t_yr_ind2006 t_yr_ind2007  t_yr_ind2008 tier1_assets ///
, a(holding_id year size) cl(holding_id)

/************************************************************
Table 5: Mortgage lending
******************************************************* */

u "$replication\Data\bank_holding_company_data", clear

tab year, gen(yr_ind)

forvalues i=2002/2008 {
	
	local yrloop = `i' - 2001
	rename yr_ind`yrloop' yr_ind`i'	
	gen t_yr_ind`i' = treated * yr_ind`i'		
}
*

* Column (1)
reghdfe loans_over_total t_yr_ind2002 t_yr_ind2003 t_yr_ind2005 ///
t_yr_ind2006 t_yr_ind2007  t_yr_ind2008 ///
, a(holding_id year) cl(holding_id)

* Column (2)
reghdfe loans_over_total t_yr_ind2002 t_yr_ind2003 t_yr_ind2005 ///
t_yr_ind2006 t_yr_ind2007  t_yr_ind2008 ///
, a(holding_id year size) cl(holding_id)

* Column (3)
reghdfe loans_over_total t_yr_ind2002 t_yr_ind2003 t_yr_ind2005 ///
t_yr_ind2006 t_yr_ind2007  t_yr_ind2008 tier1_assets ///
, a(holding_id year size) cl(holding_id)


/************************************************************
Table 6: Mortgage securitization
******************************************************* */
u "$replication\Data\bank_holding_company_data", clear

tab year, gen(yr_ind)

forvalues i=2002/2008 {
	
	local yrloop = `i' - 2001
	rename yr_ind`yrloop' yr_ind`i'	
	gen t_yr_ind`i' = treated * yr_ind`i'		
}
*

reghdfe mort_sec_all_sec_ratio t_yr_ind2002 t_yr_ind2003 t_yr_ind2005 ///
t_yr_ind2006 t_yr_ind2007  t_yr_ind2008 ///
, a(holding_id year) cl(holding_id)

reghdfe mort_sec_all_sec_ratio t_yr_ind2002 t_yr_ind2003 t_yr_ind2005 ///
t_yr_ind2006 t_yr_ind2007  t_yr_ind2008 ///
, a(holding_id year size) cl(holding_id)

reghdfe mort_sec_all_sec_ratio t_yr_ind2002 t_yr_ind2003 t_yr_ind2005 ///
t_yr_ind2006 t_yr_ind2007  t_yr_ind2008 tier1_assets ///
, a(holding_id year size) cl(holding_id)


/************************************************************
Table 7: Repo borrowings and repo rates 
******************************************************* */

u "$replication\Data\triparty_repo_04_06", clear

gen treated = 1 if collateral_class == "MBS"
replace treated = 0 if collateral_class == "Govt. (agency,treasuries)"

gen scaled_repo_maturity = repo_maturity/360
gen post_x_treated = post_bapcpa * treated 
egen mmmf_x_ibank = group(cik_number counterparty) 

********** repo amount due 
replace amount_due = amount_due/1000000000

reg amount_due post_x_treated treated post_bapcpa scaled_repo_maturity if treated ~=., ro 

reghdfe amount_due post_x_treated treated post_bapcpa scaled_repo_maturity if treated ~=., a(counterparty cik_number) cl(mmmf_x_ibank)

********** repo rate
replace repo_rate = repo_rate * 100

reg repo_rate post_x_treated treated post_bapcpa scaled_repo_maturity if treated ~=., ro 

reghdfe repo_rate post_x_treated treated post_bapcpa scaled_repo_maturity if treated ~=., a(counterparty cik_number) cl(mmmf_x_ibank)


/************************************************************
Table 8: Repo haircuts 
******************************************************* */
u "$replication\Data\triparty_repo_04_06", clear

gen treated = 1 if collateral_class == "MBS"
replace treated = 0 if collateral_class == "Govt. (agency,treasuries)"

gen scaled_repo_maturity = repo_maturity/360
gen post_x_treated = post_bapcpa * treated 
egen mmmf_x_ibank = group(cik_number counterparty) 

replace haircut = haircut * 100

reg haircut post_x_treated treated post_bapcpa scaled_repo_maturity if treated ~=., ro 

reghdfe haircut post_x_treated treated post_bapcpa scaled_repo_maturity if treated ~=., a(counterparty cik_number) cl(mmmf_x_ibank)

/************************************************************
Table A.3: HFS securities 
******************************************************* */

u "$replication\Data\bank_holding_company_data", clear

tab year, gen(yr_ind)

forvalues i=2002/2008 {
	
	local yrloop = `i' - 2001
	rename yr_ind`yrloop' yr_ind`i'	
	gen t_yr_ind`i' = treated * yr_ind`i'		
}
*

reghdfe pvt_mbs_htm t_yr_ind2002 t_yr_ind2003 t_yr_ind2005 ///
t_yr_ind2006 t_yr_ind2007  t_yr_ind2008 ///
, a(holding_id year) cl(holding_id)

reghdfe pvt_mbs_htm t_yr_ind2002 t_yr_ind2003 t_yr_ind2005 ///
t_yr_ind2006 t_yr_ind2007  t_yr_ind2008 ///
, a(holding_id year size) cl(holding_id)

reghdfe pvt_mbs_htm t_yr_ind2002 t_yr_ind2003 t_yr_ind2005 ///
t_yr_ind2006 t_yr_ind2007  t_yr_ind2008 tier1_assets ///
, a(holding_id year size) cl(holding_id)

/************************************************************
Table A.4: Business model differences
******************************************************* */

u "$replication\Data\FRY11_data", clear
merge m:1 holding_id year using "$replication\Data\bank_holding_company_data", keepusing(treated)

tab year, gen(yr_ind)

forvalues i=2002/2008 {
	
	local yrloop = `i' - 2001
	rename yr_ind`yrloop' yr_ind`i'	
	gen t_yr_ind`i' = treated * yr_ind`i'		
}

xtile size = BHCS2170, nq(5)

*
gen trading_rev = BHCSA220/BHCS2170
la var trading_rev "A220/2170"

gen i_bank_fees = BHCSB490/BHCS2170
la var i_bank_fees "B490/2170"

reghdfe trading_rev t_yr_ind2002 t_yr_ind2003 t_yr_ind2005 ///
t_yr_ind2006 t_yr_ind2007  t_yr_ind2008 ///
, a(holding_id year) cl(holding_id)

reghdfe trading_rev t_yr_ind2002 t_yr_ind2003 t_yr_ind2005 ///
t_yr_ind2006 t_yr_ind2007  t_yr_ind2008 ///
, a(holding_id year size) cl(holding_id)

reghdfe i_bank_fees t_yr_ind2002 t_yr_ind2003 t_yr_ind2005 ///
t_yr_ind2006 t_yr_ind2007  t_yr_ind2008 ///
, a(holding_id year) cl(holding_id)

reghdfe i_bank_fees t_yr_ind2002 t_yr_ind2003 t_yr_ind2005 ///
t_yr_ind2006 t_yr_ind2007  t_yr_ind2008 ///
, a(holding_id year size) cl(holding_id)


/************************************************************
Table A.5: High and low ex-ante mortgage activity 
******************************************************* */

u "$replication\Data\bank_holding_company_data", clear

capture drop yr_ind* t_yr_ind*

tab year, gen(yr_ind)

forvalues i=2002/2008 {
	
	local yrloop = `i' - 2001
	rename yr_ind`yrloop' yr_ind`i'	
	gen t_yr_ind`i' = treated_on_mtg_lending * yr_ind`i'		
}
*

reghdfe pvt_mbs t_yr_ind2002 t_yr_ind2003 t_yr_ind2005 ///
t_yr_ind2006 t_yr_ind2007  t_yr_ind2008 ///
, a(holding_id year) cl(holding_id)

reghdfe pvt_mbs t_yr_ind2002 t_yr_ind2003 t_yr_ind2005 ///
t_yr_ind2006 t_yr_ind2007  t_yr_ind2008 ///
, a(holding_id year size) cl(holding_id)

reghdfe pvt_mbs t_yr_ind2002 t_yr_ind2003 t_yr_ind2005 ///
t_yr_ind2006 t_yr_ind2007  t_yr_ind2008 tier1_assets ///
, a(holding_id year size) cl(holding_id)


/************************************************************
Table A.6: High and low ex-ante securitization activity 
******************************************************* */

u "$replication\Data\bank_holding_company_data", clear

capture drop yr_ind* t_yr_ind*

tab year, gen(yr_ind)

forvalues i=2002/2008 {
	
	local yrloop = `i' - 2001
	rename yr_ind`yrloop' yr_ind`i'	
	gen t_yr_ind`i' = treated_on_sec_activity * yr_ind`i'		
}
*

reghdfe pvt_mbs t_yr_ind2002 t_yr_ind2003 t_yr_ind2005 ///
t_yr_ind2006 t_yr_ind2007  t_yr_ind2008 ///
, a(holding_id year) cl(holding_id)


reghdfe pvt_mbs t_yr_ind2002 t_yr_ind2003 t_yr_ind2005 ///
t_yr_ind2006 t_yr_ind2007  t_yr_ind2008 ///
, a(holding_id year size) cl(holding_id)


reghdfe pvt_mbs t_yr_ind2002 t_yr_ind2003 t_yr_ind2005 ///
t_yr_ind2006 t_yr_ind2007  t_yr_ind2008 tier1_assets ///
, a(holding_id year size) cl(holding_id)

/************************************************************
Table A.7: Structured securitization issuance
******************************************************* */

u "$replication\Data\CMO_issuances", clear

collapse (sum) OrigMM (mean) ibank (first) Type, by(bank_id year type_id)

gen log_orig = log(1+OrigMM)

tabstat OrigMM if type_id == 4 & ibank == 0, by(year) s(mean p50 min max)

tabstat OrigMM if type_id == 4 & ibank == 1, by(year) s(mean p50 min max)

log close